package com.ecom.common.dao;

import com.ecom.common.model.*;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class ProductDao {
	private JdbcTemplate jdbcTemplate;

//    private static final Logger logger = Logger.getLogger(UserDao.class);

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int checkDatabase() {
        String sqlString = "select count(*) from TESTCONNECTION";

        int result = jdbcTemplate.queryForInt(sqlString);

        return result;
    }

    public List<Category> getCategoryList() {

        String sqlString = "select cid, category_name from category";

        List<Category> categories = jdbcTemplate.query(sqlString, new RowMapper<Category>() {
            public Category mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                Category category = new Category();
                category.setCid(rs.getInt(1));
                category.setCategoryName(rs.getString(2));
                return category;
            }
        });

        return categories;
    }

    public List<Product> getBookListFromCategory(Category category) {

        String sqlString = "select P.pid, P.product_name, p.product_desc, B.author, B.publisher " +
                "  from Product p, BOOk B, Product_Belong pb " +
                "  where pb.pid = p.pid and p.pid = B.pid and pb.sid in " +
                "  (select S.sid from Sub_category S, Category C " +
                "  where S.cid = C.cid and c.cid = ?) ";

        List<Product> books = jdbcTemplate.query(sqlString, new Object[]{category.getCid()}, new RowMapper<Product>() {
            public Product mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                Book book = new Book();
                book.setPID(rs.getInt(1));
                book.setProductName(rs.getString(2));
                book.setProductDec(rs.getString(3));
                book.setAuthor(rs.getString(4));
                book.setPublisher(rs.getString(5));

                return book;
            }
        });

        return books;
    }

    public List<Product> getMovieListFromCategory(Category category) {

        String sqlString = "select distinct P.pid, P.product_name, p.product_desc, " +
                "  m.actor, m.director, m.subtitle, m.mpaa_rating " +
                "  from Product p, Movie M, Product_Belong pb " +
                "  where pb.pid = p.pid and p.pid = m.pid and pb.sid in " +
                "  (select S.sid from Sub_category S, Category C" +
                "  where S.cid = C.cid and c.cid = ?) ";

        List<Product> movies = jdbcTemplate.query(sqlString, new Object[]{category.getCid()}, new RowMapper<Product>() {
            public Product mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                Movie movie = new Movie();
                movie.setPID(rs.getInt(1));
                movie.setProductName(rs.getString(2));
                movie.setProductDec(rs.getString(3));
                movie.setActor(rs.getString(4));
                movie.setDirector(rs.getString(5));
                movie.setSubtitle(rs.getString(6));
                movie.setMpaarating(rs.getString(7));

                return movie;
            }
        });

        return movies;
    }

    public List<SubCategory> getSubCategoryList(Category category) {
        String sqlString = " select s.sid, s.cid, s.sub_name from category c, sub_category s " +
                           " where c.cid = s.cid and c.cid = ? ";

        List<SubCategory> subCategories = jdbcTemplate.query(sqlString, new Object[]{category.getCid()}, new RowMapper<SubCategory>() {
            public SubCategory mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                SubCategory subCategory = new SubCategory();
                subCategory.setSid(rs.getInt(1));
                subCategory.setCid(rs.getInt(2));
                subCategory.setSubCategoryName(rs.getString(3));
                return subCategory;
            }
        });

        return  subCategories;
    }

    public List<Product> getBookListFromSubCategory(SubCategory subCategory) {

        String sqlString = "select P.pid, P.product_name, p.product_desc, B.author, B.publisher " +
                "  from Product p, BOOk B, Product_Belong pb " +
                "  where pb.pid = p.pid and p.pid = B.pid and pb.sid = ? ";

        List<Product> books = jdbcTemplate.query(sqlString, new Object[]{subCategory.getSid()}, new RowMapper<Product>() {
            public Product mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                Book book = new Book();
                book.setPID(rs.getInt(1));
                book.setProductName(rs.getString(2));
                book.setProductDec(rs.getString(3));
                book.setAuthor(rs.getString(4));
                book.setPublisher(rs.getString(5));

                return book;
            }
        });

        return books;
    }

    public List<Product> getMovieListFromSubCategory(SubCategory subCategory) {

        String sqlString = "select P.pid, P.product_name, p.product_desc, m.actor, " +
                "  m.director, m.subtitle, m.mpaa_rating " +
                "  from Product p, Movie M, Product_Belong pb " +
                "  where pb.pid = p.pid and p.pid = m.pid and pb.sid = ? ";

        List<Product> movies = jdbcTemplate.query(sqlString, new Object[]{subCategory.getSid()}, new RowMapper<Product>() {
            public Product mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                Movie movie = new Movie();
                movie.setPID(rs.getInt(1));
                movie.setProductName(rs.getString(2));
                movie.setProductDec(rs.getString(3));
                movie.setActor(rs.getString(4));
                movie.setDirector(rs.getString(5));
                movie.setSubtitle(rs.getString(6));
                movie.setMpaarating(rs.getString(7));

                return movie;
            }
        });

        return movies;
    }
}
